package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import dto.Relatorio;

public class GerarRelatorioDAO {
	
	
private Connection conexao = FabricaConexoesBanco.getInstancia().getConexao();

	
	public Iterator<Relatorio> gerarRelatorio(String dataInicial, String dataFinal) {
		String sql = 
			"SELECT "+
				"SUM(O.QUANTIDADE), "+
				"C.NOME, "+
				"DATE_FORMAT(MOV.DATA, '%m/%Y') "+
			"FROM "+
				"TIPO_OBJETO O, CDD C, MOVIMENTACAO MOV "+

			"WHERE "+ 
				"O.ID_OBJETO =  MOV.ID_OBJETO "+
				"AND C.ID_CDD = MOV.ID_CDD "+
				"AND MOV.DATA >= STR_TO_DATE(?,'%m/%d/%Y') " +
				"AND MOV.DATA <= STR_TO_DATE(?,'%m/%d/%Y') "+ 
			"GROUP BY "+
				"C.NOME, "+
				"DATE_FORMAT(MOV.DATA, '%M/%Y'); ";
		
		List<Relatorio> list = new ArrayList<Relatorio>();

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);
			pstmt.setString(1, dataInicial);
			pstmt.setString(2, dataFinal);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {

				Relatorio rel = new Relatorio();
				rel.setCdd(rs.getString("cdd"));
				rel.setAnoMes(rs.getString("anomes"));
				rel.setQtdeObjeto(rs.getInt("total"));
				
				list.add(rel);

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list.iterator();
	}

}
